﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System.Collections.Generic;
using NPOI.HSSF.Util;

namespace ExtSite
{
    public class Data2Excel
    {
        public static MemoryStream Export2Excel(object datas, Stream stOut, string templatepath)
        {

            //string filepath = string.Format( "{0}.xls",Guid.NewGuid().ToString());
            FileStream fs = new FileStream(templatepath, FileMode.Open, FileAccess.Read);
            FileInfo myfile = new FileInfo(templatepath);
            HSSFWorkbook workbook = new HSSFWorkbook(fs, true);
            ToExcel(datas, ref workbook);
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            fs.Close();
            fs.Dispose();
            //File.Delete(filepath);
            //stOut.Write(ms.GetBuffer(), 0, ms.GetBuffer().Length);
            return ms;
        }
        public static void fromExcel(string fullPath)
        {
            ExcelHelper excelHelper = new ExcelHelper();
            if (excelHelper.OpenExcel(fullPath))
            {

                for (int i = 0; i < excelHelper.GetLastSheetNum(); i++)
                {
                    excelHelper.OpenSheet(i);

                   // DataTable dt = excelHelper.GetDTFromExcel();
                 //   BLLIM.InsertToDB(dt, _date, excelHelper.GetSheetName(i), HttpContext.Current.User.Identity.Name);
                 
                 
                }
            }
        }
        public static void ToExcel(object objects, ref HSSFWorkbook workbook)
        {
            /* 
             * tạo excel cần khai báo 3 thuoc tính sau
            */
            ISheet sheet = workbook.GetSheetAt(0);
            IRow row, rowHeader;
           ICell cell, cellHeader;
            var newDataFormat = workbook.CreateDataFormat();// dùng dê format kieu gia tri trong cell : datetime,number...

            /* 
             * tạo cellstyle dê format màu mè,ke khung cho cell
            */
            ICellStyle cellstyle, cellstyleInt, cellstyleDouble, cellstyleDatetime, cellstyleHeader;
            cellstyle = workbook.CreateCellStyle();

            cellstyle.BorderBottom = BorderStyle.THIN;// tạo border
            cellstyle.BorderTop = BorderStyle.THIN;
            cellstyle.BorderRight = BorderStyle.THIN;
            cellstyle.BorderLeft = BorderStyle.THIN;
            cellstyle.Alignment = HorizontalAlignment.RIGHT;

            cellstyleInt = workbook.CreateCellStyle();
            cellstyleInt.BorderBottom = BorderStyle.THIN; // tạo border
            cellstyleInt.BorderTop = BorderStyle.THIN;
            cellstyleInt.BorderRight = BorderStyle.THIN;
            cellstyleInt.BorderLeft = BorderStyle.THIN;
            cellstyleInt.Alignment = HorizontalAlignment.RIGHT;
            cellstyleInt.DataFormat = newDataFormat.GetFormat("#,###");// định nghỉa format data cho cell : datetime, string, number
            // cellstyleInt.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,###");


            cellstyleDouble = workbook.CreateCellStyle();
            cellstyleDouble.BorderBottom = BorderStyle.THIN; // tạo border
            cellstyleDouble.BorderTop = BorderStyle.THIN;
            cellstyleDouble.BorderRight = BorderStyle.THIN;
            cellstyleDouble.BorderLeft = BorderStyle.THIN;
            cellstyleDouble.Alignment = HorizontalAlignment.RIGHT;
            // cellstyleDouble.DataFormat = newDataFormat.GetFormat("#,###.00");// định nghỉa format data cho cell : datetime, string, number
            cellstyleDouble.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");

            cellstyleDatetime = workbook.CreateCellStyle();
            cellstyleDatetime.BorderBottom = BorderStyle.THIN; // tạo border
            cellstyleDatetime.BorderTop = BorderStyle.THIN;
            cellstyleDatetime.BorderRight = BorderStyle.THIN;
            cellstyleDatetime.BorderLeft = BorderStyle.THIN;
            cellstyleDatetime.Alignment = HorizontalAlignment.RIGHT;
            cellstyleDatetime.DataFormat = newDataFormat.GetFormat("d/m/yyyy");// định nghỉa format data cho cell : datetime, string, number


            cellstyleHeader = workbook.CreateCellStyle();
            cellstyleHeader.BorderBottom = BorderStyle.THIN;
            cellstyleHeader.BorderTop = BorderStyle.THIN;
            cellstyleHeader.BorderRight = BorderStyle.THIN;
            cellstyleHeader.BorderLeft = BorderStyle.THIN;
            cellstyleHeader.Alignment = HorizontalAlignment.RIGHT;
            cellstyleHeader.FillPattern = FillPatternType.SOLID_FOREGROUND; // dung dê tô màu nguyên 1 khung cell
            cellstyleHeader.FillForegroundColor = HSSFColor.ORANGE.LIGHT_ORANGE.index;

            /* 
            * lấy đối tượng đầu tiên
           */
            object objFrist;
            objFrist = objects.GetType().GetMethod("get_Item").Invoke(objects, new object[] { 0 });

            /* 
            * tạo 1 list dể lưu tru nhưng trường được xuất ra excel trong 1 bảng
            */
            List<string> sFields = new List<string>();
            //if (Data2Excel.GB_Objects_info == null)
            //{
            //    var db = new MierukaDataClassesDataContext();
            //    Data2Excel.GB_Objects_info = db.Object_infos.ToList();
            //    db.Connection.Close();
            //}

            /* 
            * lay danh sach cac trường trong bang can xuất excel, GB_Objects_info : 1 view
            */
            //List<Object_info> fields = (from oi in Data2Excel.GB_Objects_info
            //                            where oi.Name == objFrist.GetType().Name && (oi.Show_in_excel == true || oi.Show_in_excel == null)
            //                            select oi).ToList();
            //foreach (Object_info oi in fields)
            //{
            //    sFields.Add(oi.Property_name);
            //}
            //if (fields == null || fields.Count <= 0)
            //{
            //    foreach (System.Reflection.PropertyInfo pro in objFrist.GetType().GetProperties())
            //    {
            //        sFields.Add(pro.Name);
            //    }
            //}

            int numRows = int.Parse(objects.GetType().GetProperty("Count").GetValue(objects, null).ToString());
            //int numCols = fields.Count;
            int numCols = sFields.Count;
            int indexCol = 0; // dung để đếm số cột trong excel, dê xử lý trong qua trinh append cột.
            for (int i = 0; i < numRows; i++)
            {
                object obj1;
                obj1 = objects.GetType().GetMethod("get_Item").Invoke(objects, new object[] { i });// lấy đối tượng theo thứ tự i
                for (int j = 0; j < obj1.GetType().GetProperties().Count(); j++)
                {
                    //  lấy field trong bảng cần xuất ra excel để tim trong list sFields( list chua các field cần xuất)
                    //  xem coi field đó co trong list sFields không.
                    object result = sFields.Find(item => item == obj1.GetType().GetProperties()[j].Name);
                    if (result != null)
                    {

                        if (i == 0)
                        {
                            // ghi phần header cho file excel
                            rowHeader = sheet.CreateRow(i);

                            cellHeader = rowHeader.CreateCell(indexCol);
                            cellHeader.CellStyle = cellstyleHeader; // set style dinh nghĩa o trên cho nhung cell phần header
                            cellHeader.SetCellValue(obj1.GetType().GetProperties()[j].Name); // set value cho cell
                        }

                        row = sheet.CreateRow(i + 1);
                        cell = row.CreateCell(indexCol);

                        if (obj1.GetType().GetProperties()[j].PropertyType.FullName.Contains("DateTime"))// nếu field đó la datetime
                        {

                            sheet.SetColumnWidth(indexCol, 15 * 256);
                            cell.CellStyle = cellstyleDatetime;
                            if (obj1.GetType().GetProperties()[j].GetValue(obj1, null) != null)
                            {
                                DateTime value = (DateTime)obj1.GetType().GetProperties()[j].GetValue(obj1, null);
                                if (value != null)
                                //cell.SetCellValue(DateTime.Parse( String.Format("{0:d/M/yyyy}", 
                                //                   obj1.GetType().GetProperties()[j].GetValue(obj1, null))) );
                                {
                                    cell.SetCellValue(String.Format("{0:d/M/yyyy}", value));
                                }
                            }

                        }
                        else if (obj1.GetType().GetProperties()[j].PropertyType.FullName.Contains("Int32"))// nếu field đó la datetime
                        {

                            sheet.SetColumnWidth(indexCol, 20 * 256);
                            cell.SetCellType(CellType.NUMERIC); 
                            cell.CellStyle = cellstyleInt;
                            cell.SetCellValue(
                                                  obj1.GetType().GetProperties()[j].GetValue(obj1, null) != null ?
                                                 double.Parse(String.Format("{0:#,0}", obj1.GetType().GetProperties()[j].GetValue(obj1, null))) : 0);



                        }
                        else if (obj1.GetType().GetProperties()[j].PropertyType.FullName.Contains("Double"))// nếu field đó la datetime
                        {
                            sheet.SetColumnWidth(indexCol, 20 * 256);
                            cell.SetCellType(CellType.NUMERIC);

                            cell.CellStyle = cellstyleDouble;
                            cell.SetCellValue(
                                                  obj1.GetType().GetProperties()[j].GetValue(obj1, null) != null ?
                                                 double.Parse(String.Format("{0:#,0.##}", obj1.GetType().GetProperties()[j].GetValue(obj1, null))) : 0);
                        }
                        else
                        {
                            //Regex regex = new Regex(@"^[-+]?[0-9]*\.?[0-9]+$");
                            //string tempStrValue = obj1.GetType().GetProperties()[j].GetValue(obj1, null) != null ?
                            //                           obj1.GetType().GetProperties()[j].GetValue(obj1, null).ToString() : "";
                            //if(regex.IsMatch(tempStrValue)){
                            //    sheet.SetColumnWidth(indexCol, 20 * 256);
                            //    cell.SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
                            //    cell.CellStyle = cellstyleInt;
                            //    cell.SetCellValue(
                            //                          obj1.GetType().GetProperties()[j].GetValue(obj1, null) != null ?
                            //                         double.Parse(String.Format("{0:#,0}", obj1.GetType().GetProperties()[j].GetValue(obj1, null))) : 0);

                            //}else{
                            cell.CellStyle = cellstyle;
                            cell.SetCellValue(
                                                   obj1.GetType().GetProperties()[j].GetValue(obj1, null) != null ?
                                                   obj1.GetType().GetProperties()[j].GetValue(obj1, null).ToString() : "");


                            //}
                        }

                        indexCol++;
                    }

                }
                indexCol = 0;
            }
            sheet.ForceFormulaRecalculation = true;
            //return workbook;
        }
    }
}
